#!/bin/bash
# 作用：DWD层，首日数据装载（ODS -> DWD）

# 1.判断参数
if [ $# -lt 1 ]
then
	echo "请输入正确参数表名/all" && exit
fi

# 2.定义时间属性
[ "$2" ] && dateStr=$2 || dateStr=`date -d '-1 day' +%F`

# 3.执行导数SQL

dwd_interaction_favor_add_inc="insert overwrite table dwd_interaction_favor_add_inc partition (dt='${dateStr}')
select data.id,
       data.user_id,
       data.sku_id,
       date_format(data.create_time,'yyyy-MM-dd'),
       data.create_time
from ods_favor_info_inc
where dt='${dateStr}' and type='insert';"
dwd_tool_coupon_used_inc="insert overwrite table dwd_tool_coupon_used_inc partition (dt='${dateStr}')
select data.id,
       data.coupon_id,
       data.user_id,
       data.order_id,
       date_format(data.used_time,'yyyy-MM-dd'),
       data.used_time
from ods_coupon_use_inc
where dt='${dateStr}' and type='update' and data.coupon_status='1403' and array_contains(map_keys(old),'used_time');"
dwd_trade_cart_add_inc="with
cart as (
    select data.id,
           data.user_id,
           data.sku_id,
           date_format(data.create_time,'yyyy-MM-dd') date_id,
           data.create_time,
           data.source_id,
           data.source_type,
           data.sku_num
    from ods_cart_info_inc
    where dt='${dateStr}'
    and (type='insert' or (type='update' and old['sku_num'] is not null and data.sku_num > cast(old['sku_num'] as int)))
),dic24 as (
    select dic_code,dic_name
    from ods_base_dic_full
    where dt='${dateStr}' and parent_code='24'
)
insert overwrite table dwd_trade_cart_add_inc partition (dt='${dateStr}')
select id,
       user_id,
       sku_id,
       date_id,
       create_time,
       source_id,source_type,
       dic24.dic_name,
       sku_num
from cart
left join dic24 on cart.source_type = dic24.dic_code;"
dwd_trade_cart_full="insert overwrite table dwd_trade_cart_full partition (dt='${dateStr}')
select id,
       user_id,
       sku_id,
       sku_name,
       sku_num
from ods_cart_info_full
where dt='${dateStr}';"
dwd_trade_order_detail_inc="with
od as (
    select data.id,
           data.order_id,
           data.sku_id,
           date_format(data.create_time,'yyyy-MM-dd') date_id,
           data.create_time,
           data.source_id,
           data.source_type,
           data.sku_num,
           data.sku_num * data.order_price split_original_amount,
           data.split_activity_amount,
           data.split_coupon_amount,
           data.split_total_amount
    from ods_order_detail_inc
    where dt='${dateStr}' and type='insert'
),oi as (
    select data.id,
           data.user_id,
           data.province_id
    from ods_order_info_inc
    where dt='${dateStr}' and type='insert'
),oa as (
    select order_detail_id,
           activity_id,
           activity_rule_id
    from (
             select data.order_detail_id,
                    data.activity_id,
                    data.activity_rule_id,
                    row_number() over (partition by data.order_detail_id) rn
             from ods_order_detail_activity_inc
             where dt = '${dateStr}'
               and type = 'insert'
    ) t1
    where rn=1
),oc as (
    select distinct data.order_detail_id,
           data.coupon_id
    from ods_order_detail_coupon_inc
    where dt='${dateStr}' and type='insert'
),dic24 as (
    select dic_code,dic_name
    from ods_base_dic_full
    where dt='${dateStr}' and parent_code='24'
)
insert overwrite table dwd_trade_order_detail_inc partition (dt='${dateStr}')
select od.id,
       od.order_id,
       oi.user_id,
       od.sku_id,
       oi.province_id,
       oa.activity_id,
       oa.activity_rule_id,
       oc.coupon_id,
       od.date_id,
       od.create_time,
       od.source_id,
       od.source_type,
       dic24.dic_name,
       od.sku_num,
       od.split_original_amount,
       od.split_activity_amount,
       od.split_coupon_amount,
       od.split_total_amount
from od
left join oi on od.order_id = oi.id
left join oa on od.id = oa.order_detail_id
left join oc on od.id = oc.order_detail_id
left join dic24 on od.source_type = dic24.dic_code;"
dwd_trade_pay_detail_suc_inc="with
pay as (
    select data.order_id,
           data.user_id,
           data.payment_type,
           data.callback_time,
           date_format(data.callback_time,'yyyy-MM-dd') date_id
    from ods_payment_info_inc
    where dt='${dateStr}' and type='update' and data.payment_status='1602' and array_contains(map_keys(old),'payment_status')
),oi as (
    select data.id,
           data.province_id
    from ods_order_info_inc
    where (dt='${dateStr}' or dt=date_sub('${dateStr}',1)) and (type='bootstrap-insert' or type='insert')
),od as (
    select data.id,
           data.order_id,
           data.sku_id,
           date_format(data.create_time,'yyyy-MM-dd') date_id,
           data.create_time,
           data.source_id,
           data.source_type,
           data.sku_num,
           data.sku_num * data.order_price split_original_amount,
           data.split_activity_amount,
           data.split_coupon_amount,
           data.split_total_amount
    from ods_order_detail_inc
    where (dt='${dateStr}' or dt=date_sub('${dateStr}',1)) and (type='bootstrap-insert' or type='insert')
),oa as (
    select order_detail_id,
           activity_id,
           activity_rule_id
    from (
             select data.order_detail_id,
                    data.activity_id,
                    data.activity_rule_id,
                    row_number() over (partition by data.order_detail_id) rn
             from ods_order_detail_activity_inc
             where (dt='${dateStr}' or dt=date_sub('${dateStr}',1)) and (type='bootstrap-insert' or type='insert')
    ) t1
    where rn=1
),oc as (
    select distinct data.order_detail_id,
           data.coupon_id
    from ods_order_detail_coupon_inc
    where (dt='${dateStr}' or dt=date_sub('${dateStr}',1)) and (type='bootstrap-insert' or type='insert')
),dic24 as (
    select dic_code,dic_name
    from ods_base_dic_full
    where dt='${dateStr}' and parent_code='24'
),dic11 as (
    select dic_code,dic_name
    from ods_base_dic_full
    where dt='${dateStr}' and parent_code='11'
)
insert overwrite table dwd_trade_pay_detail_suc_inc partition (dt='${dateStr}')
select t1.id,
       t1.order_id,
       t1.user_id,
       t1.sku_id,
       oi.province_id,
       oa.activity_id,
       oa.activity_rule_id,
       oc.coupon_id,
       t1.payment_type,
       dic11.dic_name,
       t1.date_id,
       t1.callback_time,
       t1.source_id,
       t1.source_type,
       dic24.dic_name,
       t1.sku_num,
       t1.split_original_amount,
       t1.split_activity_amount,
       t1.split_coupon_amount,
       t1.split_total_amount
from (
         select od.id,
                od.order_id,
                pay.user_id,
                od.sku_id,
                pay.payment_type,
                pay.date_id,
                pay.callback_time,
                od.source_id,
                od.source_type,
                od.sku_num,
                od.split_original_amount,
                od.split_activity_amount,
                od.split_coupon_amount,
                od.split_total_amount
         from od
         join pay on od.order_id = pay.order_id
) t1
left join oi on t1.order_id = oi.id
left join oa on t1.id = oa.order_detail_id
left join oc on t1.id = oc.coupon_id
left join dic11 on t1.payment_type = dic11.dic_code
left join dic24 on t1.source_type = dic24.dic_code;"
dwd_trade_trade_flow_acc="set hive.exec.dynamic.partition.mode=nonstrict;
with
old as (
   select order_id,
          user_id,
          province_id,
          order_date_id,
          order_time,
          payment_date_id,
          payment_time,
          finish_date_id,
          finish_time,
          order_original_amount,
          order_activity_amount,
          order_coupon_amount,
          order_total_amount,
          payment_amount
   from dwd_trade_trade_flow_acc
    where dt='9999-99-99'
),oi as (
    select data.id,
           data.user_id,
           data.province_id,
           date_format(data.create_time,'yyyy-MM-dd') date_id,
           data.create_time,
           null payment_date_id,
           null payment_time,
           null finish_date_id,
           null finish_time,
           data.original_total_amount,
           data.activity_reduce_amount,
           data.coupon_reduce_amount,
           data.total_amount,
           null payment_amount
    from ods_order_info_inc
    where dt='${dateStr}' and type='insert'
),pay as (
    select data.order_id,
           data.callback_time,
           date_format(data.callback_time,'yyyy-MM-dd') payment_date_id,
           data.total_amount
    from ods_payment_info_inc
    where dt='${dateStr}' and type='update' and array_contains(map_keys(old),'payment_status') and data.payment_status='1602'
),os as (
    select data.order_id,
           data.operate_time,
           date_format(data.operate_time,'yyyy-MM-dd') finish_date_id
    from ods_order_status_log_inc
    where dt='${dateStr}' and type='insert' and data.order_status='1004'
)
insert overwrite table dwd_trade_trade_flow_acc partition (dt)
select t1.order_id,
       t1.user_id,
       t1.province_id,
       t1.order_date_id,
       t1.order_time,
       nvl(t1.payment_date_id,pay.payment_date_id),
       nvl(t1.payment_time,pay.callback_time),
       nvl(t1.finish_date_id,os.finish_date_id),
       nvl(t1.finish_time,os.operate_time),
       t1.order_original_amount,
       t1.order_activity_amount,
       t1.order_coupon_amount,
       t1.order_total_amount,
       nvl(t1.payment_amount,pay.total_amount),
       nvl(os.finish_date_id,'9999-99-99') dt
from (
         select order_id,
                user_id,
                province_id,
                order_date_id,
                order_time,
                payment_date_id,
                payment_time,
                finish_date_id,
                finish_time,
                order_original_amount,
                order_activity_amount,
                order_coupon_amount,
                order_total_amount,
                payment_amount
         from old
         union all
         select id,
                user_id,
                province_id,
                date_id,
                create_time,
                payment_date_id,
                payment_time,
                finish_date_id,
                finish_time,
                original_total_amount,
                activity_reduce_amount,
                coupon_reduce_amount,
                total_amount,
                payment_amount
         from oi
) t1
left join pay on t1.order_id = pay.order_id
left join os on t1.order_id = os.order_id;"
dwd_traffic_page_view_inc="insert overwrite table dwd_traffic_page_view_inc partition (dt='${dateStr}')
select ar,ba,ch,is_new,md,mid,os,uid,vc,item,item_type,last_page_id,page_id,source_type,date_id,view_time,
       concat(mid,'-',last_value(flag,true) over(partition by mid order by view_time)) session_id,
       during_time
from (
         select common.ar,
                common.ba,
                common.ch,
                common.is_new,
                common.md,
                common.mid,
                common.os,
                common.uid,
                common.vc,
                page.item,
                page.item_type,
                page.last_page_id,
                page.page_id,
                page.source_type,
                date_format(from_utc_timestamp(ts, 'Asia/Shanghai'), 'yyyy-MM-dd') date_id,
                from_utc_timestamp(ts, 'Asia/Shanghai')                            view_time,
                page.during_time,
                if(page.last_page_id is null,ts,null) flag
         from ods_log_inc
         where dt = '${dateStr}' and page.during_time is not null
     ) t1;"
dwd_user_login_inc="with
login as (
    select uid,
           date_id,
           view_time,
           ch,
           ar,
           vc,
           ba,
           md,
           os,
           mid
from (
         select uid,
                date_id,
                view_time,
                ch,
                ar,
                vc,
                ba,
                md,
                os,
                mid,
                row_number() over (partition by session_id order by view_time) rn
         from (
                  select uid,
                         date_id,
                         view_time,
                         ch,
                         ar,
                         vc,
                         ba,
                         md,
                         os,
                         mid,
                         concat(mid, '-', last_value(flag, true) over (partition by mid order by ts)) session_id
                  from (
                           select common.uid,
                                  date_format(from_utc_timestamp(ts, 'Asia/Shanghai'), 'yyyy-MM-dd') date_id,
                                  from_utc_timestamp(ts, 'Asia/Shanghai')                            view_time,
                                  common.ch,
                                  common.ar,
                                  common.vc,
                                  common.ba,
                                  common.md,
                                  common.os,
                                  common.mid,
                                  page.last_page_id,
                                  page.page_id,
                                  if(page.last_page_id is null, ts, null)                            flag,
                                  ts
                           from ods_log_inc
                           where dt = '${dateStr}'
                             and page.page_id is not null
                       ) t1
              ) t2
         where uid is not null
     ) t3
where rn=1
),po as (
    select id,area_code
    from ods_base_province_full
    where dt='${dateStr}'
)
insert overwrite table dwd_user_login_inc partition (dt='${dateStr}')
select uid,
       date_id,
       view_time,
       ch,
       po.id,
       vc,
       mid,
       ba,
       md,
       os
from login
join po on login.ar=po.area_code;"
dwd_user_register_inc="with
ui as (
    select data.id,
           date_format(data.create_time,'yyyy-MM-dd') date_id,
           data.create_time
    from ods_user_info_inc
    where dt='${dateStr}' and type='insert'
),pa as (
    select common.uid,
           common.ch,
           common.ar,
           common.vc,
           common.mid,
           common.ba,
           common.md,
           common.os
    from ods_log_inc
    where dt='${dateStr}' and page.page_id is not null and page.page_id='register' and common.uid is not null
),po as (
    select id,area_code
    from ods_base_province_full
    where dt='${dateStr}'
)
insert overwrite table dwd_user_register_inc partition (dt='${dateStr}')
select ui.id,
       ui.date_id,
       ui.create_time,
       pa.ch,
       po.id,
       pa.vc,
       pa.mid,
       pa.ba,
       pa.md,
       pa.os
from ui
left join pa on ui.id=pa.uid
left join po on pa.ar=po.area_code;"

case $1 in
"all")
	$HIVE_HOME/bin/hive -e "${dwd_interaction_favor_add_inc}${dwd_tool_coupon_used_inc}${dwd_trade_cart_add_inc}${dwd_trade_cart_full}${dwd_trade_order_detail_inc}${dwd_trade_pay_detail_suc_inc}${dwd_trade_trade_flow_acc}${dwd_traffic_page_view_inc}${dwd_user_login_inc}${dwd_user_register_inc}"
;;
"dwd_interaction_favor_add_inc")
    $HIVE_HOME/bin/hive -e "${dwd_interaction_favor_add_inc}"
;;
"dwd_tool_coupon_used_inc")
    $HIVE_HOME/bin/hive -e "${dwd_tool_coupon_used_inc}"
;;
"dwd_trade_cart_add_inc")
    $HIVE_HOME/bin/hive -e "${dwd_trade_cart_add_inc}"
;;
"dwd_trade_cart_full")
    $HIVE_HOME/bin/hive -e "${dwd_trade_cart_full}"
;;
"dwd_trade_order_detail_inc")
    $HIVE_HOME/bin/hive -e "${dwd_trade_order_detail_inc}"
;;
"dwd_trade_pay_detail_suc_inc")
    $HIVE_HOME/bin/hive -e "${dwd_trade_pay_detail_suc_inc}"
;;
"dwd_trade_trade_flow_acc")
    $HIVE_HOME/bin/hive -e "${dwd_trade_trade_flow_acc}"
;;
"dwd_traffic_page_view_inc")
    $HIVE_HOME/bin/hive -e "${dwd_traffic_page_view_inc}"
;;
"dwd_user_login_inc")
    $HIVE_HOME/bin/hive -e "${dwd_user_login_inc}"
;;
"dwd_user_register_inc")
    $HIVE_HOME/bin/hive -e "${dwd_user_register_inc}"
;;
esac
